<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use DB;
use Carbon\Carbon;
use Cache;
use Validator;
use App\Models\ReportSetting;

class SumReport extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'sum:report {table} {date?} {--shour=} {--ehour=} {--test} {--pdi}';


    protected $fieldsConfig = [];

    //要统计的表
    protected $table;

    //小时表
    protected $hourTable;

    //月表
    protected $monthTable;

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'generate report data';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();

    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $this->fieldsConfig = $this->getFields();

        if(!$this->fieldsConfig) return;

        $isTest = $this->option('test');


        if($isTest) {
            $data = $this->getTestData();
        }else{
            $data = $this->getPrevHourData();
        }

        list($hourTable, $monthTable) = $this->getTableName($this->table);

        $this->hourTable = $hourTable;

        $this->monthTable = $monthTable;

        //插入表数据
        $this->saveData($data);
    }

    //获取统计表名
    protected function getTableName($table) {

        $suffix = str_replace('t_hisdata_', '', $table);

        $hourTable = 't_hour_'.$suffix;

        $monthTable = 't_month_'.$suffix;

        return [ $hourTable, $monthTable ];
    }

    //获取统计字段
    protected function getFields() {


        $table = $this->argument('table');

        $this->table = $table;
        
        $find = ReportSetting::where('report_table_name', $table)->first();

        if(!$find) return ;

        $fields = $find->settingField()->get();

        return $fields;
    }

    //是否是累计字段
    protected function isInc($val) {
        return data_get($val, 'is_inc')==1;
    }

    //是否是累计字段 根据字段名
    protected function isIncByField($val) {
        $incFields = $this->getIncField();
        return in_array($val, $incFields);
    }

    //规范统计字段
    protected function normalizeField() {
        $fields = $this->fieldsConfig;

        $result = [];

        foreach($fields as $k=>$v) {
            $field=$v->sum_field;
            $result[]=$this->isInc($v)?$this->normalizeInc($field):$this->normalizeAvg($field);
        }

        return implode(", ", $result);
    }

    // avg字段
    protected function normalizeAvg($field) {
        $prefixField = "hd_{$field}";
        return "round(sum({$prefixField})/count({$prefixField}),2) as {$field}";
    }

    // inc字段
    protected function normalizeInc($field) {
        $prefixField = "hd_{$field}";
        return "max({$prefixField}) as {$field}";
    }

    // 累计字段
    protected function getIncField() {

        $fields = $this->fieldsConfig->toArray();

        $filterFields = array_filter($fields, function($item){
            return $this->isInc($item);
        });

        return array_column($filterFields, 'sum_field');
    }

    //获取上一小时数据
    protected function getPrevHourData() {

        $inputDate = $this->argument('date');

        $lastHour = $inputDate;

        if(!$inputDate) {
            $inputDate = date('Y-m-d H:i:s');
            $lastHour = date('Y-m-d H', strtotime('-1 hour', strtotime($inputDate)));
        }
        
        $sHour = "{$lastHour}:00:00";
        $eHour = "{$lastHour}:59:59";

        $inputHourStart = $this->option('shour');
        $inputHourEnd = $this->option('ehour');

        if($inputHourStart) {
            $sHour = $inputHourStart;
        }

        if($inputHourEnd) {
            $eHour = $inputHourEnd;
        }

        $where='hd_datetime between ? and ? ';
        $params = [$sHour, $eHour];

        $pdi = $this->option('pdi');

        if($pdi) {
            $where .= " and pdi_index = {$pdi} ";
        }

        $searchField = $this->normalizeField();
        
        $sql=<<<EOT
        select 
            pdi_index,
            date_format(hd_datetime, '%Y-%m-%d %H') as hours,
            $searchField
            from {$this->table}
            where $where
            group by pdi_index, hours
EOT;
        $data = DB::select($sql, $params);
        if(!$data) {
            $this->info("没有查询到数据 {$inputDate}");
            return [];
        }
        return $data;
    }

    //保存数据
    protected function saveData($data) {
        if(!$data){
            return false;
        }

        $result = [];
        $result1 = [];
        foreach ($data as $v) {
            list($date, $hour) = explode(" ", data_get($v, 'hours'));
            $temp = (array)$v;
            unset($temp['hours']);

            array_set($temp, 'at_date', $date);

            $temp = array_merge($temp, $this->getIncValue($temp));
            $result[] = array_merge($temp, [ 'hour'=>$hour ]);
            $result1[] = $temp;
        }
        DB::table($this->hourTable)->insert($result);

        $fields = $this->fieldsConfig->toArray();

        $this->batchInsert($result1, $this->monthTable, array_column($fields, 'sum_field'));
    }



    //累积供水量
    protected function getIncValue($v) {

        $date = data_get($v, 'at_date');

        $pdi = data_get($v, 'pdi_index');

        $yes = $this->getYesterdayLastHourData($date, $pdi);

        if(!$yes) return [];

        $fields = $this->getIncField();

        $result = [];

        foreach($fields as $vv) {
            $result[$vv] =  data_get($v, $vv) - data_get($yes, $vv, 0);
        }

        return $result; 
    }



    //获取累计字段昨天最后一次数据
    protected function getYesterdayLastHourData($date, $pdiIndex) {

        $yesterday = Carbon::parse($date)->subDay()->toDateString();

        $cacgeKey = 'day_hour_'.$yesterday.'_'.$pdiIndex;

        $cacheValue = Cache::get($cacgeKey);

       $this->info('_'.$pdiIndex.'_'.$date.'_'.$yesterday);

        if($cacheValue!==null){
            return $cacheValue;
        }

        $incFields = $this->getIncField();

        if(!$incFields) {
            $incFields = ['*'];
        }

        $info = DB::table($this->hourTable)
            ->where('at_date', $yesterday)
            ->where('pdi_index', $pdiIndex)
            ->select($incFields)
            ->orderBy('hour', 'desc')
            ->first();
    
        Cache::put($cacgeKey, $info, now()->endOfDay());

        return $info;
    }
    
    //格式化更新字段
    protected function formatUpdateFields($v) {
        if($this->isIncByField($v)) {
            return "{$v}=values({$v})";
        }
        return "{$v}={$v}+values({$v})";
    }
    
    //保存monthData
    protected function batchInsert($data, $table, $upFields=[], $isCustom=false) {
        $first = array_keys($data[0]);
        $first[]='num';
        $fields = "`".implode('`,`', $first)."`";
        $values="";
        $sql="insert into {$table} ({$fields}) values ";
        foreach ($data as $v) {
            $v['num']=1;
            $values .= ($values?',':'')."('".implode("','", array_values($v))."')";
        }
        $updates = '';
        if(!$isCustom) {

            foreach ($upFields as $v) {
                $updates .= ($updates?',':'').$this->formatUpdateFields($v);
            }
            
            $updates .=", num=num+1";
            
        }else{
            $updates = $isCustom;
        }

        DB::insert($sql.$values." ON DUPLICATE KEY UPDATE {$updates};");
    }


    //测试数据
    protected function getTestData() {

        $inputDate = $this->argument('date');

        $lastHour = $inputDate;

        if(!$inputDate) {
            $inputDate = date('Y-m-d H:i:s');
            $lastHour = date('Y-m-d H', strtotime('-1 hour', strtotime($inputDate)));
        }


        $sHour = "{$lastHour}:00:00";
        $eHour = "{$lastHour}:59:59";

        $inputHourStart = $this->option('shour');
        $inputHourEnd = $this->option('ehour');

        if($inputHourStart) {
            $sHour = $inputHourStart.':00:00';
        }

        if($inputHourEnd) {
            $eHour = $inputHourEnd.':59:59';
        }
        $i = $sHour;
        $result = [];
        while(strtotime($i) < strtotime($eHour)) {
            $ss = $i;
            $ee = Carbon::parse($i)->addDay()->toDateTimeString();
            if(strtotime($ee)>strtotime($eHour)) {
                $ee = $eHour;
            }
            $result = array_merge($result, $this->getTestHours($ss, $ee));
            $i = $ee;
        }
        return $result;
    }

    //生成小时数据
    protected function getTestHours($s, $e) {

        $result = [];

        $pdi = $this->option('pdi');

        $fields = $this->fieldsConfig->pluck('sum_field')->all();

        while(strtotime($s)<= strtotime($e)) {

            $ee = Carbon::parse($s)->addHour()->toDateTimeString();

            if(strtotime($ee)>strtotime($e)) {
                $break;
            }
            $temp = [];
            foreach($fields as $ff) {
                $temp[$ff] = randomFloat(10, 40);
            }
            $temp['pdi_index'] = $pdi?:'123456';
            $temp['hours'] = Carbon::parse($s)->format('Y-m-d H');
            $result[] = $temp;

            $s=$ee;
        }

        return $result;
    }
}
